# 1、合并收集上来的xx年xx月收集数据.xlsx
# 2、计算每个员工当月的绩效系数，存为总表xx年xx月员工绩效系数.xlsx
# 3、生成一个项目明细表，连同横道图放到同一个html中
# 3、获取项目名称和项目实际进度，绘制横道图，存到本地xx年xx月项目进度.html

# month_evaluate.py
import os
import pandas as pd
from datetime import datetime
import plotly.express as px

# ======== 1. 配置 ========
INIT_DATA_PATH = "E:/工作临时文件/Evaluate_data/init_data"
MONTH_EVALUATE_PATH = "E:/工作临时文件/Evaluate_data/Month_data/month_workers_evaluate"
MONTH_PROJECT_RESULT_PATH = "E:/工作临时文件/Evaluate_data/Month_data/month_project_result"

# 项目类型难度系数 (文档表2)
PROJECT_DIFFICULTY = {
    "项目后评价类": 1.00,
    "投资总体评价类": 1.34,
    "项目及企业管理咨询类": 2.11,
    "企业投资规划与战略规划": 2.62,
    "投资、并购及资产重组类": 1.57,
    "能源经济政策与商业模式类": 3.14,
    "低碳技术经济咨询类": 3.31,
    "电力市场改革研究类": 4.24,
    "配合其他部门单个项目财务测算": 0.2,
    "前期项目策划：项目建议书": 0.15,
    "前期项目策划：项目可研或投标": 0.18,
    "前期项目策划：一整套流程（建议书、可研、PPT等）":0.2
}


def get_current_month_filename():
    """获取当前年月的文件名"""
    now = datetime.now()
    year = f"{now.year}年"
    month = f"{now.month-1:02d}月"
    return year + month + "收集数据.xlsx", year + month

def merge_sheets(file_path):
    """读取Excel文件前14个sheet，合并为一个DataFrame"""
    xls = pd.ExcelFile(file_path)
    all_sheets = xls.sheet_names[:14]
    df_list = [pd.read_excel(xls, sheet_name=s) for s in all_sheets]
    merged_df = pd.concat(df_list, ignore_index=True)
    return merged_df


def build_project_employee_matrix(df):
    """返回：项目-员工矩阵 DataFrame + 员工总绩效"""
    start_idx = df.columns.get_loc("蔡康康")
    emp_cols = df.columns[start_idx:]  # 所有员工列

    project_rows = []  # 每行是 {项目名称, emp1:score, emp2:score…}

    for _, row in df.iterrows():
        proj_name = str(row.get("项目名称", "未命名项目"))
        proj_type = str(row.get("项目类型", "")).strip()
        difficulty = PROJECT_DIFFICULTY.get(proj_type, 1.0)

        proj_dict = {"项目名称": proj_name}
        for emp in emp_cols:
            try:
                raw_val = float(row[emp]) if pd.notna(row[emp]) else 0.0
            except:
                raw_val = 0.0
            proj_dict[emp] = raw_val * difficulty  # 未参与=0
        project_rows.append(proj_dict)

    # 形成矩阵表
    proj_df = pd.DataFrame(project_rows).fillna(0.0)

    # 计算每个员工总绩效（列和）
    emp_totals = proj_df[emp_cols].sum(axis=0)
    total_row = {"项目名称": "总计"}
    total_row.update(emp_totals.to_dict())

    # 追加到最后一行
    proj_df = pd.concat([proj_df, pd.DataFrame([total_row])], ignore_index=True)

    return proj_df, emp_cols

def save_employee_excel_from_matrix(proj_df, emp_cols, month_str):
    """从矩阵表提取总计行生成员工总绩效Excel"""
    total_row = proj_df[proj_df["项目名称"] == "总计"].iloc[0]
    emp_df = pd.DataFrame({
        "员工": emp_cols,
        "月度绩效得分": [total_row[e] for e in emp_cols]
    })
    os.makedirs(MONTH_EVALUATE_PATH, exist_ok=True)
    save_path = os.path.join(MONTH_EVALUATE_PATH, f"{month_str}员工绩效系数.xlsx")
    emp_df.to_excel(save_path, index=False)
    print(f"✅ 员工月度绩效系数表已保存: {save_path}")

def draw_progress_gantt(df, month_str):
    df_plot = df[["项目名称", "实际进度"]].copy()
    df_plot["实际进度"] = df_plot["实际进度"].astype(str).str.replace("%", "").astype(float)
    return px.bar(df_plot,
                  x="实际进度",
                  y="项目名称",
                  orientation="h",
                  color="实际进度",
                  color_continuous_scale="Greens",
                  title=f"{month_str} 项目进度横道图")

def build_html_with_matrix_and_chart(proj_df, fig, month_str):
    os.makedirs(MONTH_PROJECT_RESULT_PATH, exist_ok=True)
    html_path = os.path.join(MONTH_PROJECT_RESULT_PATH, f"{month_str}项目进度.html")

    table_html = proj_df.to_html(index=False, float_format="%.2f")
    fig_html = fig.to_html(full_html=False, include_plotlyjs='cdn')

    full_html = f"""
    <html>
      <head><meta charset="utf-8"><title>{month_str} 项目绩效矩阵+进度图</title></head>
      <body>
        <h1>{month_str} 项目-员工绩效矩阵</h1>
        <p>行 = 项目，列 = 员工，单元格 = 员工在该项目的绩效系数；最后一行为总计</p>
        {table_html}
        <hr>
        <h1>{month_str} 项目进度横道图</h1>
        {fig_html}
      </body>
    </html>
    """
    with open(html_path, "w", encoding="utf-8") as f:
        f.write(full_html)
    print(f"✅ 项目矩阵+进度图HTML已生成: {html_path}")

def main():
    file_name, month_str = get_current_month_filename()
    file_path = os.path.join(INIT_DATA_PATH, file_name)
    if not os.path.exists(file_path):
        print(f"❌ 本月文件不存在: {file_path}")
        return

    print(f"📂 正在读取: {file_path}")
    merged_df = merge_sheets(file_path)

    # 1️⃣ 生成项目-员工绩效矩阵 + 员工列列表
    proj_df, emp_cols = build_project_employee_matrix(merged_df)

    # 2️⃣ 保存员工总绩效Excel
    save_employee_excel_from_matrix(proj_df, emp_cols, month_str)

    # 3️⃣ 项目进度横道图
    fig = draw_progress_gantt(merged_df, month_str)

    # 4️⃣ 拼接到HTML
    build_html_with_matrix_and_chart(proj_df, fig, month_str)


if __name__ == "__main__":
    main()
